import { getDeviceParameterDb } from '../lib/device-parameter-database';

interface TableMetadata {
  table_name: string;
  table_code: string;
  short_name: string;
  description: string;
  source_file: string;
}

// 参数表的元数据映射，包含简写名称
const parameterTablesMetadata: TableMetadata[] = [
  // 基础通用表
  { table_name: 'table_c2', table_code: 'C.2', short_name: '表 C.2', description: '对象命名表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c4', table_code: 'C.4', short_name: '表 C.4', description: '位置信息表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c6', table_code: 'C.6', short_name: '表 C.6', description: '位置信息表', source_file: '属性表-基础参数表.csv' },
  
  // 橇装置相关
  { table_name: 'table_c14', table_code: 'C.14', short_name: '表 C.14', description: '橇基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 泵类设备
  { table_name: 'table_c18', table_code: 'C.18', short_name: '表 C.18', description: '离心泵基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c27', table_code: 'C.27', short_name: '表 C.27', description: '其他泵基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c30', table_code: 'C.30', short_name: '表 C.30', description: '滑片泵基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c36', table_code: 'C.36', short_name: '表 C.36', description: '潜油泵基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 储罐设备
  { table_name: 'table_c81', table_code: 'C.81', short_name: '表 C.81', description: '储罐基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 鹤管设备
  { table_name: 'table_c109_1', table_code: 'C.109-1', short_name: '表 C.109-1', description: '鹤管基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 空调设备
  { table_name: 'table_c144', table_code: 'C.144', short_name: '表 C.144', description: '空调设备基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 管道设备
  { table_name: 'table_c150', table_code: 'C.150', short_name: '表 C.150', description: '管线/管道基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 阻火器
  { table_name: 'table_c173', table_code: 'C.173', short_name: '表 C.173', description: '阻火器基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 呼吸阀
  { table_name: 'table_c174', table_code: 'C.174', short_name: '表 C.174', description: '呼吸阀基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 波纹管
  { table_name: 'table_c178', table_code: 'C.178', short_name: '表 C.178', description: '金属波纹管基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 软管
  { table_name: 'table_c179', table_code: 'C.179', short_name: '表 C.179', description: '软管基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 过滤器
  { table_name: 'table_c183', table_code: 'C.183', short_name: '表 C.183', description: '管道过滤器基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 阀门类
  { table_name: 'table_c201', table_code: 'C.201', short_name: '表 C.201', description: '通用阀门基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c202', table_code: 'C.202', short_name: '表 C.202', description: '球阀基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c203', table_code: 'C.203', short_name: '表 C.203', description: '闸阀基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c204', table_code: 'C.204', short_name: '表 C.204', description: '蝶阀基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c207', table_code: 'C.207', short_name: '表 C.207', description: '止回阀基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c208', table_code: 'C.208', short_name: '表 C.208', description: '安全阀/泄压阀基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c212', table_code: 'C.212', short_name: '表 C.212', description: '水力控制阀基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 仪表设备 - 监控系统
  { table_name: 'table_c252', table_code: 'C.252', short_name: '表 C.252', description: '仪表和自控设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c254', table_code: 'C.254', short_name: '表 C.254', description: 'SCADA系统基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c255', table_code: 'C.255', short_name: '表 C.255', description: '站控系统基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c259', table_code: 'C.259', short_name: '表 C.259', description: '泄漏检测系统基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c260', table_code: 'C.260', short_name: '表 C.260', description: '消防控制系统基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c264', table_code: 'C.264', short_name: '表 C.264', description: '可燃及有毒气体检测报警系统基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c265', table_code: 'C.265', short_name: '表 C.265', description: '安全仪表系统基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 仪表设备 - 测量仪表
  { table_name: 'table_c271', table_code: 'C.271', short_name: '表 C.271', description: '温度计基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c273', table_code: 'C.273', short_name: '表 C.273', description: '温度变送器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c277', table_code: 'C.277', short_name: '表 C.277', description: '压力表基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c279', table_code: 'C.279', short_name: '表 C.279', description: '压力差压变送器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c280', table_code: 'C.280', short_name: '表 C.280', description: '差压变送器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c281', table_code: 'C.281', short_name: '表 C.281', description: '压力变送器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c284', table_code: 'C.284', short_name: '表 C.284', description: '电磁流量计基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c285', table_code: 'C.285', short_name: '表 C.285', description: '科里奥利质量流量计基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 仪表设备 - 液位检测
  { table_name: 'table_c305', table_code: 'C.305', short_name: '表 C.305', description: '液位变送器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c306', table_code: 'C.306', short_name: '表 C.306', description: '磁致伸缩液位变送器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c313', table_code: 'C.313', short_name: '表 C.313', description: '伺服液位变送器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c315', table_code: 'C.315', short_name: '表 C.315', description: '物位开关基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c319', table_code: 'C.319', short_name: '表 C.319', description: '超声物位开关基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c320', table_code: 'C.320', short_name: '表 C.320', description: '在线过程分析仪表基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c324', table_code: 'C.324', short_name: '表 C.324', description: '可燃气体检测器基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 仪表设备 - 执行机构
  { table_name: 'table_c339', table_code: 'C.339', short_name: '表 C.339', description: '电动执行机构基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c341', table_code: 'C.341', short_name: '表 C.341', description: '电液联动执行机构基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c343', table_code: 'C.343', short_name: '表 C.343', description: '调节阀基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c348', table_code: 'C.348', short_name: '表 C.348', description: '调节器基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 电气设备
  { table_name: 'table_c363', table_code: 'C.363', short_name: '表 C.363', description: '燃油发电设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c367', table_code: 'C.367', short_name: '表 C.367', description: 'UPS基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c368', table_code: 'C.368', short_name: '表 C.368', description: 'EPS基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c376', table_code: 'C.376', short_name: '表 C.376', description: '电力变压器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c385', table_code: 'C.385', short_name: '表 C.385', description: '低压开关柜基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c386', table_code: 'C.386', short_name: '表 C.386', description: '配电箱基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c393', table_code: 'C.393', short_name: '表 C.393', description: '照明设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c394', table_code: 'C.394', short_name: '表 C.394', description: '普通灯具基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c395', table_code: 'C.395', short_name: '表 C.395', description: '防爆灯具基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c397', table_code: 'C.397', short_name: '表 C.397', description: '接地装置基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 通信设备
  { table_name: 'table_c413', table_code: 'C.413', short_name: '表 C.413', description: '计算机网络设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c416', table_code: 'C.416', short_name: '表 C.416', description: '火灾自动报警设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c418', table_code: 'C.418', short_name: '表 C.418', description: '感温探测器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c419', table_code: 'C.419', short_name: '表 C.419', description: '火焰探测器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c421', table_code: 'C.421', short_name: '表 C.421', description: '声光报警器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c422', table_code: 'C.422', short_name: '表 C.422', description: '周界入侵报警设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c425', table_code: 'C.425', short_name: '表 C.425', description: '工业电视监控设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c431', table_code: 'C.431', short_name: '表 C.431', description: '广播设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c432', table_code: 'C.432', short_name: '表 C.432', description: '巡检巡更设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c433', table_code: 'C.433', short_name: '表 C.433', description: '出入口管理设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c434', table_code: 'C.434', short_name: '表 C.434', description: '门禁设备基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 污水处理设备
  { table_name: 'table_c469', table_code: 'C.469', short_name: '表 C.469', description: '含油污水处理装置基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c470', table_code: 'C.470', short_name: '表 C.470', description: '生活污水处理装置基础参数表', source_file: '属性表-基础参数表.csv' },
  
  // 消防设备
  { table_name: 'table_c483', table_code: 'C.483', short_name: '表 C.483', description: '消防自动巡检装置基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c485', table_code: 'C.485', short_name: '表 C.485', description: '消防稳压给水设备基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c494', table_code: 'C.494', short_name: '表 C.494', description: '冷却水喷淋装置基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c496', table_code: 'C.496', short_name: '表 C.496', description: '立式泡沫产生器基础参数表', source_file: '属性表-基础参数表.csv' },
  { table_name: 'table_c497', table_code: 'C.497', short_name: '表 C.497', description: '泡沫比例混合装置基础参数表', source_file: '属性表-基础参数表.csv' }
];

async function reinitParameterTableMetadata() {
  try {
    const db = getDeviceParameterDb();
    const deviceParameterDb = db as any;

    console.log('开始重新初始化参数表元数据...');

    // 1. 添加 short_name 字段（如果不存在）
    try {
      deviceParameterDb.db.prepare(`
        ALTER TABLE parameter_table_metadata 
        ADD COLUMN short_name TEXT
      `).run();
      console.log('成功添加 short_name 字段');
    } catch (error: any) {
      if (error.message.includes('duplicate column name')) {
        console.log('short_name 字段已存在，跳过添加');
      } else {
        throw error;
      }
    }

    // 2. 清空现有数据
    deviceParameterDb.db.prepare('DELETE FROM parameter_table_metadata').run();
    console.log('清空现有数据');

    // 3. 插入新的元数据
    const insertStmt = deviceParameterDb.db.prepare(`
      INSERT INTO parameter_table_metadata (
        table_name, 
        table_code, 
        short_name,
        description, 
        source_file,
        created_at,
        updated_at
      ) VALUES (?, ?, ?, ?, ?, datetime('now'), datetime('now'))
    `);

    let insertedCount = 0;

    for (const metadata of parameterTablesMetadata) {
      try {
        insertStmt.run(
          metadata.table_name,
          metadata.table_code,
          metadata.short_name,
          metadata.description,
          metadata.source_file
        );
        
        console.log(`插入: ${metadata.table_name} (${metadata.short_name}) - ${metadata.description}`);
        insertedCount++;
      } catch (error) {
        console.error(`插入失败 ${metadata.table_name}:`, error);
      }
    }

    console.log(`\n成功插入了 ${insertedCount} 条参数表元数据记录`);

    // 4. 验证插入结果
    const countStmt = deviceParameterDb.db.prepare('SELECT COUNT(*) as count FROM parameter_table_metadata');
    const result = countStmt.get() as { count: number };
    console.log(`数据库中现有 ${result.count} 条记录`);

    // 5. 显示几个示例记录
    const sampleStmt = deviceParameterDb.db.prepare(`
      SELECT table_name, table_code, short_name, description 
      FROM parameter_table_metadata 
      ORDER BY table_name 
      LIMIT 5
    `);
    const samples = sampleStmt.all();
    console.log('\n示例记录:');
    samples.forEach((sample: any) => {
      console.log(`  ${sample.table_name}: ${sample.short_name} - ${sample.description}`);
    });

    console.log('\n参数表元数据重新初始化完成！');

  } catch (error) {
    console.error('重新初始化参数表元数据失败:', error);
    process.exit(1);
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  reinitParameterTableMetadata();
}